package nl.fw.yapool.sql;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * This class wraps around a {@link PreparedStatement} and allows the programmer to set parameters by name instead
 * of by index.  This eliminates any confusion as to which parameter index represents what.  This also means that
 * rearranging the SQL statement or adding a parameter doesn't involve renumbering your indices.
 * Code such as this:
 * <pre>Connection con=getConnection();
 * String query="select * from my_table where name=? or address=?";
 * PreparedStatement p=con.prepareStatement(query);
 * p.setString(1, "bob");
 * p.setString(2, "123 terrace ct");
 * ResultSet rs=p.executeQuery();</pre>
 * can be replaced with:
 * <pre>Connection con=getConnection();
 * String query="select * from my_table where name=@name or address=@address";
 * NamedParameterStatement p=new NamedParameterStatement(con, query);
 * p.setString("name", "bob");
 * p.setString("address", "123 terrace ct");
 * ResultSet rs=p.executeQuery();</pre>
 * @author adam_crume
 * <br>Copied from http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html
 * <br>Modified by (2010/03/02)
 * @author rayco_arana
 * <br>Copied from http://monetproject.git.sourceforge.net/git/gitweb.cgi?p=monetproject/monetproject;a=blob_plain;f=applications/loginservice/server/src/org/monet/loginservice/utils/NamedParameterStatement.java;hb=5325b37aa61543c6262e01b09fa5cb95b188383a
 * <br>Modified by (2012/02/01)
 * @author frederikw
 * <br>Updated some comments, included this class in DbPool:
 * <br>https://github.com/intercommit/DbPool
 * <br>Modified by (2012/12/07)
 * @author FWiers
 * <br>Made all internal static maps concurrent
 * <br>Use a separate indexCache-map when calling constructor with autoGeneratedKey (prevents NPE if same query is used). 
 * <br>Modified by (2013/07/16)
 * @author FWiers
 * <br>Added setBytes().
 * <br>Put cache in a separate class.
 */
public class NamedParameterStatement {

	/** The statement this object is wrapping. */
	private final PreparedStatement statement;
	private final String namedQuery;

	/** Maps parameter names to arrays of ints which are the parameter indices. */
	private final Map<String, List<Integer>> indexMap;
	private final String parsedQuery;

	/**
	 * Creates a NamedParameterStatement.  Wraps a call to
	 * c.{@link Connection#prepareStatement(java.lang.String) prepareStatement}.
	 * @param c the database connection
	 * @param namedQuery      the parameterized query
	 * @throws SQLException if the statement could not be created
	 */
	public NamedParameterStatement(final Connection c, final String namedQuery) throws SQLException {

		this.namedQuery = namedQuery;
		final NPSCache cache = getCache();
		NPSCache.NamedCache nc = cache.getCache(namedQuery);
		if (nc.haveCache()) {
			indexMap = nc.getIndexMap();
			parsedQuery = nc.getParsedQuery();
		} else {
			indexMap = new HashMap<String, List<Integer>>();
			parsedQuery = parse(namedQuery, indexMap);
			cache.setCache(namedQuery, parsedQuery, indexMap);
		}
		statement = c.prepareStatement(parsedQuery);
	}

	/**
	 * Creates a NamedParameterStatement.  Wraps a call to
	 * c.{@link Connection#prepareStatement(java.lang.String) prepareStatement}.
	 * @param c the database connection
	 * @param namedQuery      the parameterized query
	 * @param autoGeneratedKeys
	 * @throws SQLException if the statement could not be created
	 */
	public NamedParameterStatement(final Connection c, final String namedQuery, final int autoGeneratedKeys) throws SQLException {

		this.namedQuery = namedQuery;
		final NPSCache cache = getCache();
		String [] queries = null;
		NPSCache.NamedArrayCache nc = cache.getCache(namedQuery, autoGeneratedKeys);
		if (nc.haveCache()) {
			indexMap = nc.getIndexMap();
			queries = nc.getParsedQuery();
			parsedQuery = queries[0];
		} else {
			indexMap = new HashMap<String, List<Integer>>();
			queries = namedQuery.split(";");
			parsedQuery = parse(queries[0], indexMap);
			queries[0] = parsedQuery;
			cache.setCache(namedQuery, queries, indexMap, autoGeneratedKeys);
		}
		if (queries.length == 1) {
			statement = c.prepareStatement(parsedQuery, autoGeneratedKeys);
		} else {
			statement = c.prepareStatement(parsedQuery, new String[]{queries[1]});
		}
	}
	
	/**
	 * Called from constructors to retrieve a cache instance.
	 * Overload to use a different cache instance. 
	 * @return the default instance of {@link NPSCache}.
	 */
	protected NPSCache getCache() {
		return NPSCache.getInstance();
	}

	/**
	 * Parses a query with named parameters.  The parameter-index mappings are put into the map, and the
	 * parsed query is returned.  DO NOT CALL FROM CLIENT CODE.  This method is non-private so JUnit code can
	 * test it.
	 * @param query    query to parse
	 * @param paramMap map to hold parameter-index mappings
	 * @return the parsed query
	 */
	static final String parse(final String query, final Map<String, List<Integer>> paramMap) {
		// I was originally using regular expressions, but they didn't work well for ignoring
		// parameter-like strings inside quotes.
		final int length=query.length();
		final StringBuilder parsedQuery=new StringBuilder(length);
		boolean inSingleQuote=false;
		boolean inDoubleQuote=false;
		int index=1;

		for(int i=0;i<length;i++) {
			char c=query.charAt(i);
			if(inSingleQuote) {
				if(c=='\'') {
					inSingleQuote=false;
				}
			} else if(inDoubleQuote) {
				if(c=='"') {
					inDoubleQuote=false;
				}
			} else {
				if(c=='\'') {
					inSingleQuote=true;
				} else if(c=='"') {
					inDoubleQuote=true;
				} else if(c=='@' && i+1<length &&
						Character.isJavaIdentifierStart(query.charAt(i+1))) {
					int j=i+2;
					while(j<length && Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					final String name=query.substring(i+1,j);
					c='?'; // replace the parameter with a question mark
					i+=name.length(); // skip past the end if the parameter

					List<Integer> indexList=paramMap.get(name);
					if(indexList==null) {
						indexList=new LinkedList<Integer>();
						paramMap.put(name, indexList);
					}
					indexList.add(new Integer(index));

					index++;
				}
			}
			parsedQuery.append(c);
		}
		return parsedQuery.toString();
	}

	/**
	 * Returns the indexes for a parameter.
	 * @param name parameter name
	 * @return parameter indexes
	 * @throws IllegalArgumentException if the parameter does not exist
	 */
	private int[] getIndexes(final String name) {
		final List<Integer> indexesList = indexMap.get(name);
		if(indexesList == null) throw new IllegalArgumentException("Parameter not found: "+name);
		final int[] indexes = new int[indexesList.size()];
		for(int i=0;i<indexesList.size();i++)
			indexes[i] = indexesList.get(i);
		return indexes;
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setObject(int, java.lang.Object)
	 */
	public void setObject(final String name, final Object value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setObject(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setString(int, java.lang.String)
	 */
	public void setBytes(final String name, final byte[] value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setBytes(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setString(int, java.lang.String)
	 */
	public void setString(final String name, final String value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setString(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setInt(int, int)
	 */
	public void setInt(final String name, final int value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setInt(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setLong(int, long)
	 */
	public void setLong(final String name, final long value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setLong(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
	 */
	public void setTimestamp(final String name, final Timestamp value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setTimestamp(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IOException 
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setBinaryStream(int, java.io.InputStream)
	 */
	public void setBinaryStream(final String name, final InputStream value) throws SQLException, IOException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			int available = 0;
			if(value != null) available = value.available();
			statement.setBinaryStream(indexes[i], value, available);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name parameter name
	 * @param sqlType the SQL type code defined in <code>java.sql.Types</code>
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setNull(int, int)
	 */
	public void setNull(final String name, final int sqlType) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setNull(indexes[i], sqlType);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setDate(int, Date)
	 */
	public void setDate(final String name, final Date value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setDate(indexes[i], value);
		}      
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setFloat(int, float)
	 */
	public void setFloat(final String name, final float value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setFloat(indexes[i], value);
		}
	}

	/**
	 * Sets a parameter.
	 * @param name  parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setDouble(int, double)
	 */
	public void setDouble(final String name, final double value) throws SQLException {
		final int[] indexes=getIndexes(name);
		for(int i=0; i < indexes.length; i++) {
			statement.setDouble(indexes[i], value);
		}
	}

	/**
	 * Returns the underlying statement.
	 * @return the statement
	 */
	public PreparedStatement getStatement() {
		return statement;
	}

	/**
	 * Executes the statement.
	 * @return true if the first result is a {@link ResultSet}
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#execute()
	 */
	public boolean execute() throws SQLException {
		return statement.execute();
	}

	/**
	 * Executes the statement, which must be a query.
	 * @return the query results
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#executeQuery()
	 */
	public ResultSet executeQuery() throws SQLException {
		return statement.executeQuery();
	}

	/**
	 * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement;
	 * or an SQL statement that returns nothing, such as a DDL statement.
	 * @return number of rows affected
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#executeUpdate()
	 */
	public int executeUpdate() throws SQLException {
		return statement.executeUpdate();
	}

	/**
	 * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE statement;
	 * or an SQL statement that returns nothing, such as a DDL statement, and return a
	 * ResultSet with the generated keys. Useful for drivers that not support standard
	 * getGeneratedKeys()
	 * @return number of rows affected
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#executeUpdate()
	 */
	public ResultSet executeUpdateAndGetGeneratedKeys() throws SQLException {     
		statement.executeUpdate();
		final ResultSet results = statement.getGeneratedKeys();
		return results;
	}

	/**
	 * Closes the statement.
	 * @throws SQLException if an error occurred
	 * @see Statement#close()
	 */
	public void close() throws SQLException {
		statement.close();
	}

	/**
	 * Adds the current set of parameters as a batch entry.
	 * @throws SQLException if something went wrong
	 * @see PreparedStatement#addBatch()
	 */
	public void addBatch() throws SQLException {
		statement.addBatch();
	}

	/**
	 * Executes all of the batched statements.
	 * @return update counts for each statement
	 * @throws SQLException if something went wrong
	 * @see Statement#executeBatch()
	 */
	public int[] executeBatch() throws SQLException {
		return statement.executeBatch();
	}

	/** The original query given when this object was created. */
	public String getQuery() {
		return namedQuery;
	}
	
}